<?php
//Zend_Loader::loadFile('../application/admin/models/Template.php');
class Hotel
{
    /**
     * @var object Zend DB Adapter
     */
    private $_db;
    /**
     * @var string
     */

    public function __construct()
    {
        $this->_db = Zend_Registry::get('db');
    }

    public function listHotels()
    {
        $this->_db->query("SET NAMES 'utf8'");
    	$listHotels = $this->_db->fetchAll("
			select * from hotels			
		");

        return $listHotels;
    }


    public function getHotel($hotel_id)
    {
        $this->_db->query("SET NAMES 'utf8'");
    	$sql = "SELECT hotel_id, hotel_title, hotel_city, hotel_country, hotel_description, hotel_facility_en, hotel_facility_ru, contact_email, hotel_phone, hotel_fax, hotel_skype, no_rooms, is_Enabled
				FROM hotels 
				WHERE hotel_id =" . $hotel_id;
        
        $getHotel = $this->_db->fetchrow($sql);
        return $getHotel;
    }
    
    public function getCityByHotelId($hotel_id)
    {
    	$this->_db->query("SET NAMES 'utf8'");
    	$sql = "SELECT C.city_id, C.city_name
				FROM cities C
				LEFT JOIN hotels H ON H.hotel_country = C.country_id
				WHERE H.hotel_id = " . $hotel_id;
    	
    	$city_arr = $this->_db->fetchAll($sql);
    	
    	return $city_arr;
    }

    public function addHotel($form_values)
    {
        $hotel_title = $form_values['hotel_title'];
        $hotel_city = $form_values['hotel_city'];
        $hotel_country = $form_values['hotel_country'];
        $hotel_description = addslashes($form_values['hotel_description']);
        $hotel_facility_en = addslashes($form_values['hotel_facility_en']);
        $hotel_facility_ru = addslashes($form_values['hotel_facility_ru']);
        
        $contact_email = $form_values['contact_email'];
        $hotel_phone = $form_values['hotel_phone'];
        $hotel_fax = $form_values['hotel_fax'];
        $hotel_skype = $form_values['hotel_skype'];
        $no_of_rooms = $form_values['no_of_rooms'];
        $is_enabled = $form_values['is_enabled'];
        
        $this->_db->query( "set names 'utf8'" ) ;
        $sql="INSERT INTO hotels(hotel_title, hotel_city, hotel_country, hotel_description, hotel_facility_en, hotel_facility_ru, contact_email, hotel_phone, hotel_fax, hotel_skype, no_rooms, is_Enabled) 
              VALUES('".$hotel_title."', '".$hotel_city."', '".$hotel_country."', '".$hotel_description."', '" . $hotel_facility_en . "', '" . $hotel_facility_ru . "', '" . $contact_email . "', '" . $hotel_phone . "','" . $hotel_fax . "','" . $hotel_skype . "',
              '" . $no_of_rooms . "', " . $is_enabled . ")";
        $addhotel = $this->_db->exec($sql);
        
        if($addhotel)
        {
        	echo "Hotel details has been successfully added";
        }
    }

    public function updateHotel($form_values)
    {
        $hotel_id = $form_values['hotel_id'];
    	$hotel_title = $form_values['hotel_title'];
        $hotel_city = $form_values['hotel_city'];
        $hotel_country = $form_values['hotel_country'];
        $hotel_description = addslashes($form_values['hotel_description']);
        $hotel_facility_en = addslashes($form_values['hotel_facility_en']);
        $hotel_facility_ru = addslashes($form_values['hotel_facility_ru']);
        $contact_email = $form_values['contact_email'];
        $hotel_phone = $form_values['hotel_phone'];
        $hotel_fax = $form_values['hotel_fax'];
        $hotel_skype = $form_values['hotel_skype'];
        $no_of_rooms = $form_values['no_of_rooms'];
        $is_enabled = $form_values['is_enabled'];
    	
        $this->_db->query( "set names 'utf8'" ) ;
    	$sql = "UPDATE hotels 
        		SET hotel_title = '" . $hotel_title . "', hotel_city = " . $hotel_city . ", hotel_country = " . $hotel_country . ",
      				hotel_description = '" . $hotel_description . "', hotel_facility_en = '" . $hotel_facility_en . "', hotel_facility_ru = '" . $hotel_facility_ru . "', contact_email = '" . $contact_email . "', hotel_phone = '" . $hotel_phone . "',
      				hotel_fax = '" . $hotel_fax . "', hotel_skype = '" . $hotel_skype . "', no_rooms = " . $no_of_rooms . ", is_Enabled = '" . $is_enabled . "'
      			WHERE hotel_id = " . $hotel_id;
        $editHotels = $this->_db->exec($sql);
        if($editHotels)
        {
        	echo "Hotel details has been successfully updated";
        }
    }

    public function deleteHotel($hotel_id)
    {
        $hotel_images = $this->_db->fetchAll("
        	SELECT filename
        	FROM hotel_images 
        	WHERE hotel_id = " . $hotel_id);
        
        
    	
    	$sql = "DELETE 
        		FROM hotels 
        		WHERE hotel_id = " . $hotel_id;

        $delete_hotel = $this->_db->exec($sql);

        if($delete_hotel) 
        {
        	foreach ($hotel_images as $hotel_img_arr) :
        		unlink('images/hotels/thumbs/' . $hotel_img_arr['filename']);
        		unlink('images/hotels/large/' . $hotel_img_arr['filename']);
        	endforeach;
        	
        	echo "Select hotel details has been successfully deleted";
        }

    }

    public function listCountriesforhotel()
    {
        $options = $this->_db->fetchPairs("
			select  country_id,country_name
			from countries 
			
		");

        return $options;
    }

    public function getCity($country)
    {
        $sql="select city_id, city_name from cities where country_id=".$country."";
        //echo $sql;exit;
        $options = $this->_db->fetchAll($sql);



        return $options;
    }
    
    public function assignroomcats($roomcats,$hotel_id)
    {
        $this->_db->exec("SET AUTOCOMMIT=0");
        $this->_db->exec("START TRANSACTION");
        $sql="delete from  hotelrooms  where hotel_id=".$hotel_id."";
        $deleteroomcatshotel = $this->_db->exec($sql);

        foreach ($roomcats as $value) {
            $sql1="insert into hotelrooms (hotel_id,roomcat_id) values(".$hotel_id.",".$value['roomcat_id'].")";
            //echo $sql1;exit;
            $addroomcats=$this->_db->exec($sql1);
        }

        if($deleteroomcatshotel &&  $addroomcats) {

            $this->_db->exec("COMMIT");
        }
        else {

            $this->_db->exec("ROLLBACK");

        }
        return $addroomcats;

    }

    public function getAminities()
    {
        $sql = "SELECT amenity_id, amenity_type
              FROM amenities";
        $amenity_arr = $this->_db->fetchAll($sql);

        return $amenity_arr;
    }
    
    /*public function assignAmenity($form_values, $hotel_id)
    {
    	$amenity_val = "";
    	unset($form_values['hotel_id']);
    	foreach ($form_values as $key=>$form_entry)
    	{
    		for ($i = 0; $i < sizeof($form_entry); $i++)
    		{
    			$amenity_val .= $form_entry[$i] . "|";
    		}
    	}
    	$sql = "INSERT INTO hotel_amenities(hotel_id, amenity_id) VALUES(" . $hotel_id . ", '" . $amenity_val . "')";
    	$assign_amenity = $this->_db->exec($sql);
    	if($assign_amenity)
    	{
    		echo "Amenities has been successfully assigned";
    	}
    	exit;
    }*/
    
    public function assignAmenity($form_values, $hotel_id)
    {
    	unset($form_values['hotel_id']);
    	foreach ($form_values as $key=>$form_entry)
    	{
    		for ($i = 0; $i < sizeof($form_entry); $i++)
    		{
    			$amenity_val = $form_entry[$i];
    			$sql = "INSERT INTO hotel_amenities(hotel_id, amenity_id) VALUES(" . $hotel_id . ", '" . $amenity_val . "')";
    			$assign_amenity = $this->_db->exec($sql);
    		}
    	}
    	
    	if($assign_amenity)
    	{
    		echo "Amenities has been successfully assigned";
    	}
    	exit;
    }
    
    public function getAmenitiesByHotelId($hotel_id)
    {
    	$sql = "SELECT A.amenity_type, A.amenity_id
	    		FROM amenities A	    		
	    		WHERE A.amenity_id NOT IN (SELECT HA.amenity_id FROM hotel_amenities HA WHERE HA.hotel_id=" . $hotel_id . ")";
    	
    	$amenity_arr = $this->_db->fetchAll($sql);
    	
    	return $amenity_arr;
    }
}

